"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : JDBC连接兼容B库
Case Name   : 公共函数
Create At   : 2023/10/13
Owner       : opentestcase022
Description :
    1.公共函数
Expect      :
    1.公共函数
History     :
"""
import difflib
import os
import re
import subprocess
import tempfile

from testcase.utils.CommonSH import CommonSH


class ComJDBC(CommonSH):
    def __init__(self, node_name='PrimaryDbUser'):
        super(ComJDBC, self).__init__(node_name)
        self.mysql_f_name = None
        self.og_f_name = None
        self.mysql_jar_name = None
        self.og_jar_name = None
        self.contant = ''
        self.pri_sh = CommonSH(node_name)
        self.update_list = ['create', 'alter', 'drop', 'insert', 'update',
                            'delete']
        self.query_list = ['select', 'show']
        self.cur_ip = subprocess.getoutput(
            "hostname -I|head -1|awk '{print $1}'")
        self.err_ignore = [
            r'\[\d+\.\d+\.\d+\.\d+:\d+/\d+\.\d+\.\d+\.\d+:\d+\]',
            r'\*+ \d+,\d+ \*+', r'-+ \d+,\d+ -+']

    def get_parent_dir(self, f_path, levels=1):
        self.log.info(f'原路径：{f_path}')
        for _ in range(levels):
            f_path = os.path.dirname(f_path)
        self.log.info(f'parent路径：{f_path}')
        return f_path

    @staticmethod
    def read_sql_file(f_path):
        if os.path.exists(f_path):
            with open(f_path, 'r', encoding='utf-8', errors='ignore') as f:
                contant = f.read()
        else:
            raise Exception('文件不存在，请检查！')
        return contant

    def format_case_sql(self, sql):
        comment = re.findall('--.*\n', sql, re.I)
        regex_res = re.split('|'.join(comment), sql)
        new_sql = ''
        for i in regex_res:
            if i and i.strip():
                new_sql += i.rstrip().replace('"', '\\"') + '\n'
        self.log.info(new_sql)
        return new_sql

    def write_java_file(self, **kwargs):
        if kwargs["db_type"] == 'mysql':
            self.mysql_f_name = kwargs["f_name"]
        else:
            self.og_f_name = kwargs["f_name"]
        head = 'import java.sql.*;'
        space = ' '
        db_conn_info = f'jdbc:{kwargs["db_type"]}://{kwargs["db_host"]}:' \
            f'{kwargs["db_port"]}/{kwargs["db_name"]}'
        declare_part = f'{space * 8}String url = "{db_conn_info}";\n' \
            f'{space * 8}String username = "{kwargs["db_user"]}";\n' \
            f'{space * 8}String password = "{kwargs["db_passwd"]}";\n' \
            f'{space * 8}Connection conn = null;\n' \
            f'{space * 8}Statement stmt = null;\n' \
            f'{space * 8}ResultSet rs = null;\n' \
            f'{space * 8}ResultSetMetaData rsmd = null;\n' \
            f'{space * 8}int cols = 0;\n' \
            f'{space * 8}int rows = 0;\n' \
            f'{space * 8}int rc = 0;\n' \
            f'{space * 8}Boolean rb;\n' \
            f'{space * 8}String sql = "";'
        load_driver = f'{space * 12}Class.forName("{kwargs["db_driver"]}");' \
            f'\n' if kwargs["db_type"] != 'opengauss' else ''
        conn_part = f'{load_driver}' \
            f'{space * 12}conn = DriverManager.getConnection(' \
            f'url, username, password);\n' \
            f'{space * 12}stmt = conn.createStatement();'
        sql_part = ''
        for one in kwargs["sql"].strip().split(';'):
            if one and one.strip() and '--' not in one:
                if '\n' not in one:
                    one_sql = f'{one.strip()}'
                else:
                    one_sql = f'" + "'.join([line.strip() for line in
                                             one.strip().splitlines()])
                if re.search('|'.join(self.query_list), one.lower()) and not \
                        re.search('|'.join(self.update_list), one.lower()):
                    sql_part += f'{space * 12}try {{\n' \
                        f'{space * 16}sql = "{one_sql.strip()}";\n' \
                        f'{space * 16}rs = stmt.executeQuery(sql);\n' \
                        f'{space * 16}rsmd = rs.getMetaData();\n' \
                        f'{space * 16}cols = rsmd.getColumnCount();\n' \
                        f'{space * 16}System.out.println(sql);\n' \
                        f'{space * 16}rows = 0;\n' \
                        f'{space * 16}while (rs.next()) {{\n' \
                        f'{space * 20}for(int i=1; i<cols+1; i++){{\n' \
                        f'{space * 24}if (i==cols) {{\n' \
                        f'{space * 28}System.out.print(rs.getString(i));\n' \
                        f'{space * 24}}} else {{\n' \
                        f'{space * 28}System.out.print(rs.getString(i) + ' \
                        f'"|");\n' \
                        f'{space * 24}}};\n' \
                        f'{space * 20}}}\n' \
                        f'{space * 20}System.out.println();\n' \
                        f'{space * 20}rows++;\n' \
                        f'{space * 16}}}\n' \
                        f'{space * 16}System.out.println(rows + "rows");\n' \
                        f'{space * 12}}} catch (SQLException e) {{\n' \
                        f'{space * 16}System.out.println(sql);\n' \
                        f'{space * 16}System.out.println("ERROR:" + ' \
                        f'e.getMessage());\n' \
                        f'{space * 16}e.printStackTrace();\n' \
                        f'{space * 12}}}\n\n'
                elif re.search('|'.join(self.update_list), one.lower()):
                    sql_part += f'{space * 12}try {{\n' \
                        f'{space * 16}sql = "{one_sql.strip()}";\n' \
                        f'{space * 16}rc = stmt.executeUpdate(sql);\n' \
                        f'{space * 16}System.out.println(sql);\n' \
                        f'{space * 16}if (rc>=0) {{\n' \
                        f'{space * 20}System.out.println("SQL SUCCESS");\n' \
                        f'{space * 20}}}\n' \
                        f'{space * 12}}} catch (SQLException e) {{\n' \
                        f'{space * 16}System.out.println(sql);\n' \
                        f'{space * 16}System.out.println("ERROR:" + ' \
                        f'e.getMessage());\n' \
                        f'{space * 16}e.printStackTrace();\n' \
                        f'{space * 12}}}\n\n'
                else:
                    sql_part += f'{space * 12}try {{\n' \
                        f'{space * 16}sql = "{one_sql.strip()}";\n' \
                        f'{space * 16}rb = stmt.execute(sql);\n' \
                        f'{space * 16}System.out.println(sql);\n' \
                        f'{space * 16}if (rb) {{\n' \
                        f'{space * 20}System.out.println("SQL SUCCESS");\n' \
                        f'{space * 20}}}\n' \
                        f'{space * 12}}} catch (SQLException e) {{\n' \
                        f'{space * 16}System.out.println(sql);\n' \
                        f'{space * 16}System.out.println("ERROR:" + ' \
                        f'e.getMessage());\n' \
                        f'{space * 16}e.printStackTrace();\n' \
                        f'{space * 12}}}\n\n'
        catch_class_not_found_exception = f'' \
            f'catch (ClassNotFoundException e) {{\n' \
            f'{space * 12}e.printStackTrace();\n' \
            f'{space * 8}}}' if kwargs["db_type"] != 'opengauss' else ''
        self.contant = f'{head}\n\n' \
            f'public class {kwargs["f_name"]} {{\n' \
            f'{space * 4}public static void main(String[] args) {{\n' \
            f'{declare_part}\n\n' \
            f'{space * 8}try {{\n{conn_part}\n\n{sql_part}\n' \
            f'{space * 8}}} {catch_class_not_found_exception} ' \
            f'catch (SQLException e) {{\n' \
            f'{space * 12}e.printStackTrace();\n' \
            f'{space * 8}}} finally {{\n' \
            f'{space * 12}try {{\n' \
            f'{space * 16}if (rs != null) {{rs.close();}}\n' \
            f'{space * 16}if (stmt != null) {{stmt.close();}}\n' \
            f'{space * 16}if (conn != null) {{conn.close();}}\n' \
            f'{space * 12}}} catch (SQLException e) {{\n' \
            f'{space * 16}e.printStackTrace();\n' \
            f'{space * 12}}}\n' \
            f'{space * 8}}}\n' \
            f'{space * 4}}}\n' \
            f'}}'
        self.log.info(self.contant)
        with open(f'{kwargs["f_name"]}.java', 'w+', encoding='utf-8',
                  errors='ignore') as f:
            f.write(self.contant)

    def compile_java_file(self, db_type):
        jar_name = self.mysql_jar_name if db_type == 'mysql' else \
            self.og_jar_name
        f_name = self.mysql_f_name if db_type == 'mysql' else self.og_f_name
        cmd = f'javac -encoding utf-8 -cp {jar_name} {f_name}.java'
        self.log.info(cmd)
        res = subprocess.getoutput(cmd)
        self.log.info(res)
        return res

    def run_java_file(self, db_type):
        jar_name = self.mysql_jar_name if db_type == 'mysql' else \
            self.og_jar_name
        f_name = self.mysql_f_name if db_type == 'mysql' else self.og_f_name
        cmd = f'java -cp {jar_name}:./ {f_name}'
        res = self.getoutput(cmd)
        return res

    def compare_sql_res(self, from_str, to_str, from_name, to_name):
        diff = difflib.context_diff(from_str.splitlines(),
                                    to_str.splitlines(),
                                    fromfile=from_name,
                                    tofile=to_name)
        str_diff = '\n'.join([i.strip() for i in diff])
        self.log.info('\n' + str_diff)
        return str_diff

    def compare_sql_err_res(self, from_str, to_str, from_name, to_name,
                            *ignore):
        self.log.info('-----忽略不重要信息-----')
        self.err_ignore.extend(ignore)
        for pattern in self.err_ignore:
            regex_res1 = re.sub(pattern, "", from_str)
            regex_res2 = re.sub(pattern, "", to_str)
            from_str = regex_res1
            to_str = regex_res2

        str_diff = self.compare_sql_res(from_str, to_str, from_name, to_name)
        return str_diff

    def remove_java_file(self):
        remove_list = list()
        if self.mysql_f_name:
            remove_list.append(f'{self.mysql_f_name}.java')
            remove_list.append(f'{self.mysql_f_name}.class')
        if self.og_f_name:
            remove_list.append(f'{self.og_f_name}.java')
            remove_list.append(f'{self.og_f_name}.class')
        if self.og_jar_name:
            remove_list.append(self.og_jar_name)
        if self.mysql_jar_name:
            remove_list.append(self.mysql_jar_name)
        for f in remove_list:
            f_path = os.path.join(os.getcwd(), f)
            if os.path.exists(f_path):
                os.remove(f_path)

    def get_jdbc(self, db_type):
        cmd = f"yat info|grep 'Yat home path'|awk -F ':' '{{print $2}}'"
        self.log.info(cmd)
        yat_home_path = subprocess.getoutput(cmd)
        lib_path = os.path.join(yat_home_path, 'lib')
        self.log.info(lib_path)
        cmd = f"ls {lib_path}|grep '{db_type}'|head -1"
        self.log.info(cmd)
        jar_name = subprocess.getoutput(cmd)
        self.log.info(jar_name)
        if jar_name:
            if db_type == 'mysql':
                self.mysql_jar_name = jar_name
            else:
                self.og_jar_name = jar_name
            cmd = f"\\cp {os.path.join(lib_path, jar_name)} ."
            self.log.info(cmd)
            res = subprocess.getoutput(cmd)
            self.log.info(res)
        else:
            res = None
            self.log.error(f"未找到{db_type}jar包")
        return res

    def getoutput(self, cmd, ispwd=False, is_print=True):
        if not ispwd:
            self.log.info(f'开始执行: {cmd.strip()}')
        f_out = None
        f_err = None
        tem_out = tempfile.TemporaryFile()
        tem_err = tempfile.TemporaryFile()
        try:
            sub_obj = subprocess.Popen(cmd,
                                       shell=True,
                                       encoding="utf-8",
                                       stdout=tem_out,
                                       stderr=tem_err)
            sub_obj.communicate()
            tem_out.seek(0)
            f_out = tem_out.read().decode('utf-8')
            tem_err.seek(0)
            f_err = tem_err.read().decode('utf-8')
            if f_err:
                self.log.error(f'Error: {f_err}')
            if is_print:
                self.log.info(f'Success: {f_out}')
        except Exception as e:
            self.log.error(f'Error: {str(e)}')
        finally:
            tem_out.close()
            tem_err.close()
        return f_out.strip(), f_err.strip()
